/****** Object:  Stored Procedure [dbo].spDongMoKyUpdate Script Date: 8/7/2012 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spDongMoKyUpdate]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spDongMoKyUpdate]
GO

/******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:					Description:
**		--------	--------				-----------------------------------
**		13/08/2012	SongTM					Innit created
*******************************************************************************/

CREATE PROCEDURE [dbo].[spDongMoKyUpdate]
(
	@MaKy nvarchar(8),
	@DongKy bit,
	@NguoiCapNhap nvarchar(50),
	@GhiChu nvarchar(200)
)

WITH ENCRYPTION

AS

SET NOCOUNT ON
	 
	--update dongmokychitiet
	Update tbl_Chitietdongmoky
	set tbl_Chitietdongmoky.[SoCuoiKy] = tbl_Luukho.TonKho
	from tbl_Luukho
	where tbl_Chitietdongmoky.[MaLuuKho] = tbl_Luukho.[MaLuuKho]
		AND tbl_Chitietdongmoky.MaKy = @MaKy
		
	--Insert record was input durring ky
	INSERT INTO tbl_chitietdongmoky (MaKy, MaLuuKho,SoCuoiKy)
								SELECT @MaKy, tbl_luukho.MaluuKho,tbl_luukho.TonKho
								FROM tbl_luukho
								where tbl_luukho.MaLuuKho not in 
								     (select MaLuuKho from tbl_chitietdongmoky where MaKy = @MaKy)

	--update dong mo ky
	UPDATE [tbl_DongMoKy]
	SET [DongKy] = @DongKy,
		[NguoiCapNhap] = @NguoiCapNhap,
		[GhiChu] = @GhiChu
	WHERE	[MaKy] = @MaKy

	--update total of Nhap, Xuat values in Term -->TamPM code
	declare @month as int
	declare @year as int
	set @month = CAST(RIGHT(@MaKy, 2) as int)
	set @year = CAST(LEFT(@MaKy, 4) as int)	
	declare @ChiTietNhapXuat as tblChiTietDongMoKy
	INSERT INTO @ChiTietNhapXuat (MaLuuKho, TongNhap, TongXuat, SoCuoiKy) 
								EXEC spGetTongNhapXuatValueByTerm @month, @year
	UPDATE @ChiTietNhapXuat SET MaKy = @MaKy
	
	UPDATE tbl_ChiTietDongMoKy
	SET tbl_ChiTietDongMoKy.TongNhap = t2.TongNhap,
	    tbl_ChiTietDongMoKy.TongXuat = t2.TongXuat
	FROM @ChiTietNhapXuat t2
	WHERE t2.MaKy = tbl_ChiTietDongMoKy.MaKy AND t2.MaLuuKho = tbl_ChiTietDongMoKy.MaLuuKho
	
	--assign by zero if SoDauKy or TongNhap or TongXuat is NULL
	UPDATE tbl_ChiTietDongMoKy
	SET SoDauKy = 0
	WHERE SoDauKy IS NULL
	
	UPDATE tbl_ChiTietDongMoKy
	SET TongNhap = 0
	WHERE TongNhap IS NULL
	
	UPDATE tbl_ChiTietDongMoKy
	SET TongXuat = 0
	WHERE TongXuat IS NULL

GO

--GRANT EXECUTE ON [dbo].[spDongMoKyUpdate] TO [sa]
--GO
